﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

using VirtualDrum.util;

namespace VirtualDrum.lib.db
{
	class SQLServer: ControlDatabase
	{
		/// <summary>
		/// 连接对象
		/// </summary>
		protected SqlConnection _objConn;
		/// <summary>
		/// 命令执行对象
		/// </summary>
		protected SqlCommand _objCommand;
		/// <summary>
		/// 数据读取对象
		/// </summary>
		protected SqlDataReader _objDataReader;

		/// <summary>
		/// 构造函数
		/// </summary>
		protected SQLServer():base()
		{
			this._objCommand = null;
			this.getDBConnect();
		}

		/// <summary>
		/// 得到数据库连接对象
		/// </summary>
		protected override void getDBConnect(){
			try{
				this._objConn = new SqlConnection(this.getConnString());
				LittleTools.debug("数据库链接成功！");
			}catch(SqlException ex){
				LittleTools.debug("数据库连接失败！");
				ex.GetBaseException();
			}
		}

		/// <summary>
		/// 得到数据库执行命令对象
		/// </summary>
		/// <param name="sql"></param>
		protected override void getDbCommand(string sql){
			this._objCommand = new SqlCommand(sql, this._objConn);
			if(!this._objCommand.Connection.State.ToString().Equals("Open")){
				this._objCommand.Connection.Open();
			}
			LittleTools.debug(sql);
		}

		/// <summary>
		/// 得到本类唯一实例的工厂方法
		/// </summary>
		/// <returns></returns>
		public static SQLServer getInstance()
		{
			if (SQLServer.objCDB == null)
			{
				lock(obj){
					if (SQLServer.objCDB == null)
					{
						SQLServer.objCDB = new SQLServer();
					}
				}
			}
			return (SQLServer)SQLServer.objCDB;
		}

		/// <summary>
		/// 执行有结果集返回的查询,如select
		/// </summary>
		/// <param name="sql"></param>
		/// <returns></returns>
		public override IList<string[]> executeQuery(string sql)
		{
			this.getDbCommand(sql);
			return this.makeResultArrayList();
		}				

		/// <summary>
		/// 执行没有结果集的查询,如update,insert
		/// </summary>
		/// <param name="sql"></param>
		/// <returns></returns>
		public override int executeNonQuery(string sql){
			this.getDbCommand(sql);
			return this._objCommand.ExecuteNonQuery();
		}

		/// <summary>
		/// 生成数据集列表
		/// </summary>
		/// <returns></returns>
		protected override IList<string[]> makeResultArrayList()
		{
			this._objResultAL.Clear();
			this._objDataReader = this._objCommand.ExecuteReader();
			while (this._objDataReader.HasRows && this._objDataReader.Read())
			{
				string[] objs = new string[this._objDataReader.FieldCount];
				for (int i = 0; i < objs.Length; i++)
				{
					objs[i] = this._objDataReader.GetValue(i).ToString();
				}
				//int result = this._objDataReader.GetValues(objs);
				this._objResultAL.Add(objs);
			}
			this.closeConnection();
			this._objDataReader.Close();
			return this._objResultAL;
		}

		/// <summary>
		/// 关闭当前数据库连接
		/// </summary>
		public override void closeConnection()
		{
			this._objCommand.Connection.Close();
			this._objCommand = null;
		}

		/// <summary>
		/// 从配置文件里得到连接数据库信息
		/// </summary>
		/// <returns></returns>
		protected override string getConnString()
		{
			return ConfigurationSettings.AppSettings["connectionString"];			
		}

		/// <summary>
		/// 得到系统当前时间
		/// </summary>
		/// <returns></returns>
		public override string getDateTime()
		{
			return "GetDate()";
		}
	}
}
